﻿using HandlebarsDotNet;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;
using RuoVea.DynamicWebApi;
using RuoVea.ExEnum;
using RuoVea.ExIdGen;
using RuoVea.ExSugar;
using RuoVea.ExUtil;
using RuoVea.ExUtil.Exceptions;
using RuoVea.OmiApi.Reports.Models;
using RuoVea.OmiApi.Reports.Util;
using SqlSugar;
using System.Data;

namespace RuoVea.OmiApi.Reports.Service;

/// <summary>
/// 字段管理
/// </summary>
[ApiExplorerSettings(GroupName = "Report")]
public partial class RptSqlColumnService : IApplicationService
{
    private readonly SugarRepository<RptSqlColumn> _thisRepository;

    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisRepository"></param>
    public RptSqlColumnService(SugarRepository<RptSqlColumn> thisRepository)
    {
        _thisRepository = thisRepository;
    }

    /// <summary>
    /// 根据sqlId获取查询列名
    /// </summary>
    /// <param name="sqlId"></param>
    /// <returns></returns>
    public List<RptSqlColumn> GetReportSqlColumn(long sqlId)
    {
        return _thisRepository.AsQueryable().Where(t => t.SqlId == sqlId).OrderBy(it => it.ParentId).OrderBy(it => it.OrdinalNo).ToList();
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="db"></param>
    /// <param name="sql"></param>
    /// <returns></returns>
    /// <exception cref="ParamiterException"></exception>
    [NonAction]
    public List<RptSqlColumn> GetReportMetaDataColumns(SqlSugarClient db, string sql)
    {
        // mustache 语法处理
        var template = Handlebars.Compile(sql);
        sql = template(sql);

        try
        {
            // 最简洁的写法
            DataTable dt = db.SqlQueryable<dynamic>(sql).Take(1).ToDataTable();
            return GetFieldInfoFromDataTable(dt);
        }
        catch (Exception ex)
        {
            throw new ParamiterException(ex.Message);
        }
    }

    /// <summary>
    /// 根据DataTable返回查询结果的数据结构（简化版）
    /// </summary>
    private List<RptSqlColumn> GetFieldInfoFromDataTable(DataTable dt)
    {
        List<RptSqlColumn> columnList = new();

        // 确保至少有一行数据
        if (dt.Rows.Count == 0)
        {
            dt.Rows.Add(dt.NewRow());
        }
        else
        {
            // 只保留第一行数据
            var firstRow = dt.Rows[0];
            dt.Clear();
            dt.Rows.Add(firstRow);
        }
        dt.AcceptChanges();

        // 使用Json序列化来确保驼峰命名转换
        var serializerSettings = new JsonSerializerSettings
        {
            ContractResolver = new CamelCasePropertyNamesContractResolver()
        };

        string json = JsonConvert.SerializeObject(dt, Formatting.Indented, serializerSettings);
        var list = JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(json);

        // 巧妙之处：通过序列化后的key来获取正确的驼峰命名字段名
        foreach (DataColumn column in dt.Columns)
        {
            // 查找序列化后对应的驼峰命名字段名
            string sqlField = list[0]
                .Where(kvp => string.Equals(kvp.Key, column.ColumnName, StringComparison.OrdinalIgnoreCase))
                .Select(kvp => kvp.Key)
                .FirstOrDefault() ?? column.ColumnName.FirstLower();

            var rptSqlColumn = new RptSqlColumn
            {
                Id = IdGenerator.Id,
                SqlField = sqlField,  // 使用序列化后的驼峰命名作为SqlField
                ColumnName = string.IsNullOrEmpty(column.Caption) ? column.ColumnName.FirstLower() : column.Caption,
                IsList = YesOrNot.Y,
                IsExport = YesOrNot.Y,
                IsQuery = YesOrNot.N,
                IsParameterOnly = YesOrNot.N,
                DataType = DbProvider.GetCSharpSimpleType(column.DataType)
            };

            // 设置查询类型
            if (column.DataType == typeof(string))
            {
                rptSqlColumn.QueryType = "LIKE";
            }
            else if (column.DataType == typeof(DateTime))
            {
                rptSqlColumn.QueryType = "BETWEEN";
                rptSqlColumn.HtmlType = "datetime";
            }

            columnList.Add(rptSqlColumn);
        }
        return columnList;
    }
}

